package model;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 操作Excel表格的功能类
 */

public class WriteExcelToDB {
	private Workbook wb = null;
	private Sheet sheet = null;
	private Row row = null;
	private static final String EXCEL_XLS = "xls";
	private static final String EXCEL_XLSX = "xlsx";

	// 判断Excel的版本,获取Workbook
	public static Workbook getWorkbok(InputStream in, File file) throws IOException {
		Workbook wb = null;
		if (file.getName().endsWith(EXCEL_XLS)) { // Excel 2003
			wb = new HSSFWorkbook(in);
		} else if (file.getName().endsWith(EXCEL_XLSX)) { // Excel 2007/2010
			wb = new XSSFWorkbook(in);
		}
		return wb;
	}

	/**
	 * 读取Excel表格表头的内容
	 * 
	 * @param InputStream
	 * @return String 表头内容的数组
	 */
	public String[] readExcelTitle(InputStream is, File file) {
		Sheet sheet = null;
		Row row = null;
		try {
			wb = getWorkbok(is, file);
		} catch (IOException e) {
			e.printStackTrace();
		}
		sheet = wb.getSheetAt(0);
		row = sheet.getRow(0);
		// 标题总列数
		int colNum = row.getPhysicalNumberOfCells();
		System.out.println("colNum:" + colNum);
		String[] title = new String[colNum];
		for (int i = 0; i < colNum; i++) {
			title[i] = getCellFormatValue(row.getCell((short) i));
		}
		return title;
	}

	/**
	 * 读取Excel数据内容
	 * 
	 * @param InputStream
	 * @return Map 包含单元格数据内容的Map对象
	 */
	public Map<Integer, String> readExcelContent(InputStream is, File file) {
		Map<Integer, String> content = new HashMap<Integer, String>();
		String str = "";
		try {
			wb = getWorkbok(is, file);
		} catch (IOException e) {
			e.printStackTrace();
		}
		sheet = wb.getSheetAt(0);
		// 得到总行数
		int rowNum = sheet.getLastRowNum();
		row = sheet.getRow(0);
		int colNum = row.getPhysicalNumberOfCells();
		// 正文内容应该从第二行开始,第一行为表头的标题
		for (int i = 1; i <= rowNum; i++) {
			row = sheet.getRow(i);
			int j = 0;
			while (j < colNum) {
				str += getCellFormatValue(row.getCell((short) j)).trim() + "    ";
				j++;
			}
			content.put(i, str);
			str = "";
		}
		return content;
	}

	/**
	 * 获取单元格数据内容为字符串类型的数据
	 * 
	 * @param cell Excel单元格
	 * @return String 单元格数据内容
	 */
	private String getStringCellValue(Cell cell) {
		String strCell = "";
		switch (cell.getCellType()) {
		case STRING:
			strCell = cell.getStringCellValue();
			break;
		case NUMERIC:
			strCell = String.valueOf(cell.getNumericCellValue());
			break;
		case BOOLEAN:
			strCell = String.valueOf(cell.getBooleanCellValue());
			break;
		case BLANK:
			strCell = "";
			break;
		default:
			strCell = "";
			break;
		}
		if (strCell.equals("") || strCell == null) {
			return "";
		}
		if (cell == null) {
			return "";
		}
		return strCell;
	}

	/**
	 * 根据HSSFCell类型设置数据
	 * 
	 * @param cell
	 * @return
	 */
	private String getCellFormatValue(Cell cell) {
		String cellvalue = "";
		if (cell != null) {
			// 判断当前Cell的Type
			switch (cell.getCellType()) {
			// 如果当前Cell的Type为NUMERIC
			case NUMERIC:
				// 取得当前Cell的数值
				DecimalFormat df = new DecimalFormat("0");
				cellvalue = df.format(cell.getNumericCellValue());

				break;

			// 如果当前Cell的Type为STRIN
			case STRING:
				// 取得当前的Cell字符串
				cellvalue = cell.getRichStringCellValue().getString();
				break;
			default:
				cellvalue = " ";
			}
		} else {
			cellvalue = "";
		}
		return cellvalue;

	}

	// ExcelWriteInToDB
	public static String writeExcl(String tableName, String setPos, String fileName, String newRowname)
			throws IOException {
		Connection conn = null;
		PreparedStatement ps = null;
		PreparedStatement ps2 = null;
		ResultSet rs = null;
		String insertsql = "update " + tableName + " set " + setPos + "= ? where  stuNo = ?";
		String altersql = "alter table " + tableName + " add " + newRowname + " varchar(20)";
		try {
			StringBuilder sb = new StringBuilder();
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(insertsql);
			ps2 = conn.prepareStatement(altersql);
			ps2.executeUpdate();
			File file = new File(fileName);
			WriteExcelToDB excelReader = new WriteExcelToDB();
			InputStream is2 = new FileInputStream(fileName);
			Map<Integer, String> map = excelReader.readExcelContent(is2, file);

			for (int i = 3; i <= map.size(); i++) {
				String[] s = map.get(i).split("\\s+");
				ps.setString(1, s[2]);
				ps.setString(2, s[0]);
				System.out.println(insertsql);
				int result = ps.executeUpdate();
				if (result == 0) {
					sb.append("学号      " + s[0] + "姓名    " + s[1] + " 成绩    " + s[2] + "   插入有异常" + "\n");
				}
			}
			String re = sb + "";
			return re;

		} catch (SQLException e) {
			System.out.println("SQLException!");
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			System.out.println("FileNotFound");
			e.printStackTrace();
		} finally {
			JDBCUtil.realeaseAll(rs, ps, conn);
		}
		return "插入完毕";

	}

	public static String findErr(String tablename, String finPos) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String findsql = "select stuNo,name, " + finPos + " from " + tablename + " where " + finPos + " is NULL or "
				+ finPos + " ='缺考'";
		;

		try {
			StringBuilder sb = new StringBuilder();
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(findsql);
			rs = ps.executeQuery();

			int flag = 0;
			while (rs.next()) {
				if (flag == 0) {
					sb.append("下列在名单中的学生没有成绩\n" + "学号" + "\t" + "姓名" + "\t" + "成绩" + "\t\n");
					flag = 1;
				}
				sb.append(rs.getString(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\n");

			}
			return sb + "";

		} catch (SQLException e) {
			System.out.println("SQLException!");
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.realeaseAll(rs, ps, conn);
		}
		return "查找完毕";
	}

	public static void accuAccordPercent(String tablename, String cname, Map<String, Double> per) {
		Connection conn = null;
		PreparedStatement ps = null;
		PreparedStatement ps2 = null;
		PreparedStatement ps3 = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		String altersql = "alter table " + tablename + " add " + cname + " varchar(20)";
		sb.append("update " + tablename + " set " + cname + "=");

		try {

			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(altersql);
			ps.executeUpdate();

			for (Map.Entry<String, Double> entry : per.entrySet()) {
				String clearsql = " UPDATE " + tablename + " set  " + entry.getKey() + " =0 where " + entry.getKey()
						+ "='缺考'or " + entry.getKey() + " is NULL; ";
				ps3 = conn.prepareStatement(clearsql);
				ps3.executeUpdate();
			}
			int flag = 0;
			for (Map.Entry<String, Double> entry : per.entrySet()) {
				if (flag == 0) {
					sb.append(entry.getKey() + "*" + entry.getValue() + " ");
					flag = 1;
				} else {
					sb.append("+ " + entry.getKey() + "*" + entry.getValue() + " ");
				}

			}
			String updatesql = sb + " ;";
			System.out.println(updatesql);
			ps2 = conn.prepareStatement(updatesql);
			ps2.executeUpdate();
			System.out.println("成功设置权重写入总分");

		} catch (SQLException e) {
			System.out.println("SQLException!");
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.realeaseAll(rs, ps, conn);
		}

	}

}
